import pymysql
import logging
import sys

# 加入日志
# 获取logger实例
logger = logging.getLogger("createfacelog")
# 指定输出格式
formatter = logging.Formatter('%(asctime)s\
              %(levelname)-8s:%(message)s')
# 文件日志
#file_handler = logging.FileHandler("C:\\python\\project\\ai-parents-py-app\\log/createfacelog.log")
file_handler = logging.FileHandler("./log/createfacelog.log")
file_handler.setFormatter(formatter)
# 控制台日志
console_handler = logging.StreamHandler(sys.stdout)
console_handler.setFormatter(formatter)

# 为logge添加具体的日志处理器
logger.addHandler(file_handler)
logger.addHandler(console_handler)

logger.setLevel(logging.INFO)


class DBHelper:
    # 构造函数
    def __init__(self, host='localhost', user='root',port=3306,
                 pwd='72305392^)f', db='aiparents'):
        self.host = host
        self.user = user
        self.port = port
        self.pwd = pwd
        self.db = db
        self.conn = None
        #self.conn = pymysql.connect(self.host, self.port, self.user,self.pwd, self.db, charset='utf8')
        self.cur = None

    # 连接数据库
    def connectDatabase(self):
        try:
            self.conn = pymysql.connect(host=self.host,port=self.port, user=self.user,passwd=self.pwd, database=self.db, charset='utf8')
        except:
            print("Unexpected error:", sys.exc_info()[0])
            logger.error("connectDatabase failed")
            return False
        self.cur = self.conn.cursor()
        return True

    # 关闭数据库
    def close(self):
        # 如果数据打开，则关闭；否则没有操作
        if self.conn and self.cur:
            self.cur.close()
            self.conn.close()
        return True

    # 执行数据库的sq语句,主要用来做插入操作
    def execute(self, sql, params=None):
        # 连接数据库
        self.connectDatabase()
        try:
            if self.conn and self.cur:
                # 正常逻辑，执行sql，提交操作
                self.cur.execute(sql, params)
                self.conn.commit()
        except:
            logger.error("execute failed: " , sql)
            #logger.error("params: ",params)
            self.close()
            return False
        return True

    # 用来查询表数据
    def fetchall(self, sql, params=None):
        self.execute(sql, params)
        return self.cur.fetchall()


if __name__ == '__main__':
    dbhelper = DBHelper()
    # 创建数据库的表
    #sql = """create table maoyan('id' varchar(8),'title' varchar(50),'star' varchar(200),'time' varchar(100),primary key('id'));"""
    # sql = """CREATE TABLE maoyan (
    #          FIRST_NAME  CHAR(20) NOT NULL,
    #          LAST_NAME  CHAR(20),
    #          AGE INT,
    #          SEX CHAR(1),
    #          INCOME FLOAT )"""
    #result = dbhelper.execute(sql, None)

    # dbhelper.close()
    # if result:
    #     logger.info("maoyan　table创建成功")
    # else:
    #     logger.error("maoyan　table创建失败")

    #dbhelper 插入表
    sql = "insert into ai_statistics.test values ('2','123','2021-11-10 18:30:00')"
    re = dbhelper.execute(sql,None)
    print(re)